/**
 * 
 */
package cn.com.gzheroli.core.common.service.impl;

import java.io.Serializable;
import java.text.MessageFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.ibatis.jdbc.SQL;
import org.apache.log4j.Logger;
import org.beetl.sql.core.Params;
import org.beetl.sql.core.SQLManager;
import org.beetl.sql.core.SQLReady;
import org.beetl.sql.core.SQLScript;
import org.beetl.sql.core.SQLSource;
import org.beetl.sql.core.engine.PageQuery;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import cn.com.gzheroli.cache.SpringRedisCache;
import cn.com.gzheroli.core.common.mapper.CommonMapper;
import cn.com.gzheroli.core.common.service.HeroService;
import cn.com.gzheroli.core.common.service.SystemService;
import cn.com.gzheroli.core.util.NameHandler;
import cn.com.gzheroli.core.util.HeroSqlUtils;
import cn.com.gzheroli.core.util.MyBatisSqlUtils;
import cn.com.gzheroli.core.util.NameHandler;
import cn.com.gzheroli.core.util.mybatis.SqlMapper;
import cn.com.gzheroli.tag.core.DataGrid;

/**
 * @类名: BeetlServiceImpl
 * @描述: hero公共数据库操作方法封装
 * @作者: 李长荣 lichang_20011@163.com
 * @日期: 2017年9月14日 上午7:08:31
 * @version V1.0
 * 
 */
@Service("heroService")
public class HeroServiceImpl implements HeroService {
	private static final Logger logger = Logger.getLogger(HeroServiceImpl.class);
	@Resource
	SQLManager sqlManager;
	@Resource
	private SystemService systemService;
	@Autowired
	private SpringRedisCache springRedisCache;
	@Autowired
	SqlMapper sqlMapper;
	@Resource
	CommonMapper commonMapper;

	@Override
	public <T> T get(Class<T> resultType, Object pk) {
		return sqlManager.single(resultType, pk);
	}

	@Override
	public <T> T get(Class<T> resultType, Object pk, String suffix) {
		String tableName = NameHandler.getTableName(resultType.getSimpleName());
		tableName = tableName + "_" + suffix;
		String columnName = NameHandler.getColumnName("id");
		SQL sql = new SQL();
		sql.SELECT("t1.*");
		Class superClass = resultType.getSuperclass();
		if (!superClass.getSimpleName().equals("Object")) {
			String tableName2 = NameHandler.getTableName(superClass.getSimpleName());
			sql.SELECT("t2.*");
			sql.FROM(tableName2 + " t2 ");
			sql.WHERE("t1.id=t2.id");
		}
		sql.FROM(tableName + " t1");
		if (pk != null) {
			sql.WHERE("t1." + columnName + "=#id#");
		}
		Map para = new HashMap();
		para.put("id", pk);
		return (T) this.getSQLScript(sql.toString()).selectSingle(para, resultType);
	}

	@Override
	public <T> T findUniqueByProperty(Class<T> entityClass, String propertyName, Object value, String... suffix) {
		String tableName = NameHandler.getTableName(entityClass.getSimpleName());
		if (suffix != null && suffix.length > 0) {
			tableName = tableName + "_" + suffix[0];
		}
		String columnName = NameHandler.getColumnName(propertyName);
		SQL sql = new SQL();
		sql.SELECT("t1.*");
		Class superClass = entityClass.getSuperclass();
		if (!superClass.getSimpleName().equals("Object")) {
			String tableName2 = NameHandler.getTableName(superClass.getSimpleName());
			if (suffix != null && suffix.length > 0) {
				tableName2 = tableName2 + "_" + suffix[0];
			}
			sql.SELECT("t2.*");
			sql.FROM(tableName2 + " t2 ");
			sql.WHERE("t1.id=t2.id");
		}
		sql.FROM(tableName + " t1");
		if (value != null) {
			sql.WHERE("t1." + columnName + "=#" + propertyName + "#");
		} else {
			sql.WHERE("t1." + columnName + " is null");
		}
		Map para = new HashMap();
		para.put(propertyName, value);
		return (T) this.getSQLScript(sql.toString()).selectSingle(para, entityClass);
	}
	@Override
	public <T> T findUnique(Class<T> entityClass, Map<String, Object> para, String... suffix) {
		String tableName = NameHandler.getTableName(entityClass.getSimpleName());
		if (suffix != null && suffix.length > 0) {
			tableName = tableName + "_" + suffix[0];
		}
		SQL sql = new SQL();
		sql.SELECT("t1.*");
		sql.FROM(tableName + " t1");
		Class superClass = entityClass.getSuperclass();
		if (!superClass.getSimpleName().equals("Object")) {
			String tableName2 = NameHandler.getTableName(superClass.getSimpleName());
			if (suffix != null && suffix.length > 0) {
				tableName2 = tableName2 + "_" + suffix[0];
			}
			sql.SELECT("t2.*");
			sql.FROM(tableName2 + " t2 ");
			sql.WHERE("t1.id=t2.id");
		}
		for (Map.Entry<String, Object> entry : para.entrySet()) {
			String propertyName = entry.getKey();
			String columnName = NameHandler.getColumnName(propertyName);
			if (para.get(propertyName) != null) {
				sql.WHERE("t1." + columnName + "=#" + propertyName + "#");
			} else {
				sql.WHERE("t1." + columnName + " is null");
			}
		}

		List<Map<String, Object>> mapList;
		return (T) this.getSQLScript(sql.toString()).selectSingle(para, entityClass);
	}

	@Override
	public <T> List<T> findByProperty(Class<T> entityClass, String propertyName, Object value, String... suffix) {
		String tableName = NameHandler.getTableName(entityClass.getSimpleName());
		if (suffix != null && suffix.length > 0) {
			tableName = tableName + "_" + suffix[0];
		}
		String columnName = NameHandler.getColumnName(propertyName);
		SQL sql = new SQL();
		sql.SELECT("t1.*");
		Class superClass = entityClass.getSuperclass();
		if (!superClass.getSimpleName().equals("Object")) {
			String tableName2 = NameHandler.getTableName(superClass.getSimpleName());
			if (suffix != null && suffix.length > 0) {
				tableName2 = tableName2 + "_" + suffix[0];
			}
			sql.SELECT("t2.*");
			sql.FROM(tableName2 + " t2 ");
			sql.WHERE("t1.id=t2.id");
		}
		sql.FROM(tableName + " t1");
		sql.WHERE("t1." + columnName + "=#" + propertyName + "#");
		Map para = new HashMap();
		para.put(propertyName, value);
		return sqlManager.execute(sql.toString(), entityClass, para);
	}

	@Override
	public void updateEntitie(Object pojo, Class<?>... args) {
		String tableName = NameHandler.getTableName(pojo.getClass().getSimpleName());
		Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName,pojo.getClass());
		if (args == null || args.length == 0) {
			Map<String, Object> paraMap = MyBatisSqlUtils.buildUpdateSql(pojo, tableColumns);
			this.getSQLScript(paraMap.get("sql").toString()).update(paraMap.remove("sql"));
			Class superClass = pojo.getClass().getSuperclass();
			if (!superClass.getSimpleName().equals("Object")) {
				tableName = NameHandler.getTableName(superClass.getSimpleName());
				tableColumns = systemService.getHeroTableColumns(tableName,superClass);
				Map<String, Object> paraMapSupper = MyBatisSqlUtils.buildUpdateSql(pojo, tableColumns,
						superClass.getSimpleName(), null);
				this.getSQLScript(paraMapSupper.get("sql").toString()).update(paraMapSupper.remove("sql"));
			}
		} else if (args != null && args.length == 1) {
			tableName = NameHandler.getTableName(args[0].getSimpleName());
			tableColumns = systemService.getHeroTableColumns(tableName,args[0]);
			Map<String, Object> paraMap = MyBatisSqlUtils.buildUpdateSql(pojo, tableColumns, tableName, null);
			this.getSQLScript(paraMap.get("sql").toString()).update(paraMap.remove("sql"));
			Class superClass = pojo.getClass().getSuperclass();
			if (!superClass.getSimpleName().equals("Object")) {
				tableName = NameHandler.getTableName(superClass.getSimpleName());
				tableColumns = systemService.getHeroTableColumns(tableName,superClass);
				Map<String, Object> paraMapSupper = MyBatisSqlUtils.buildUpdateSql(pojo, tableColumns, superClass);
				this.getSQLScript(paraMapSupper.get("sql").toString()).update(paraMapSupper.remove("sql"));
			}
		}

	}

	@Override
	public void save(Object entity) {
		String tableName = NameHandler.getTableName(entity.getClass().getSimpleName());
		Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName,entity.getClass());
		Map<String, Object> paraMap = MyBatisSqlUtils.buildInsertSql(entity, tableColumns);
		this.getSQLScript(paraMap.get("sql").toString()).insert(paraMap.remove("sql"));
		Class superClass = entity.getClass().getSuperclass();
		if (!superClass.getSimpleName().equals("Object")) {
			tableName = NameHandler.getTableName(superClass.getSimpleName());
			tableColumns = systemService.getHeroTableColumns(tableName,superClass);

			Map<String, Object> paraMapSupper = MyBatisSqlUtils.buildInsertSql(entity, tableColumns);
			if (paraMapSupper.containsKey("id") || paraMapSupper.containsKey("ID")
					|| paraMapSupper.containsKey("ID_")) {
				Long size = this.getCountForJdbc(
						"select count(1) from " + tableName + " where ID='" + paraMapSupper.get("id") + "'");
				if (size == 0) {
					this.getSQLScript(paraMapSupper.get("sql").toString()).insert(paraMapSupper.remove("sql"));
				}
			}

		}
	}

	@Override
	public void save(Object entity, String suffix) {
		String tableName = NameHandler.getTableName(entity.getClass().getSimpleName());
		tableName = tableName + "_" + suffix.toUpperCase();
		Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName,entity.getClass());
		Map<String, Object> paraMap = MyBatisSqlUtils.buildInsertSql(entity, tableColumns, tableName);
		this.getSQLScript(paraMap.get("sql").toString()).insert(paraMap.remove("sql"));
		Class superClass = entity.getClass().getSuperclass();
		if (!superClass.getSimpleName().equals("Object")) {
			tableName = NameHandler.getTableName(superClass.getSimpleName());
			tableName = tableName + "_" + suffix.toUpperCase();
			tableColumns = systemService.getHeroTableColumns(tableName,superClass);

			Map<String, Object> paraMapSupper = MyBatisSqlUtils.buildInsertSql(entity, tableColumns,
					superClass.getSimpleName() + "_" + suffix.toUpperCase());
			if (paraMapSupper.containsKey("id") || paraMapSupper.containsKey("ID")
					|| paraMapSupper.containsKey("ID_")) {
				Long size = this.getCountForJdbc(
						"select count(1) from " + tableName + " where ID='" + paraMapSupper.get("id") + "'");
				if (size == 0) {
					this.getSQLScript(paraMapSupper.get("sql").toString()).insert(paraMapSupper.remove("sql"));
				}
			}

		}
	}

	@Override
	public void saveField(Map<String, Object> paraMap, Class clazz) {
		String tableName = NameHandler.getTableName(clazz.getSimpleName());
		Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName,clazz);
		paraMap.remove("sql");
		SQL sql = new SQL();
		sql.INSERT_INTO(tableName);
		for (String key : paraMap.keySet()) {
			String columnName = NameHandler.getColumnName(key);
			columnName = tableColumns.get(key)==null?"":tableColumns.get(key).toString();
			/*if (tableColumns.contains(columnName)) {
				sql.VALUES(columnName, "#" + key + "#");
			} else if (tableColumns.contains(columnName + "_")) {
				sql.VALUES(columnName + "_", "#" + key + "#");
			} else {
				continue;
			}*/
			if(!columnName.equals("")) {
				sql.VALUES(columnName, "#" + key + "#");
			}
		}
		this.getSQLScript(sql.toString()).insert(paraMap);
	}

	@Override
	public void saveMap(Map<String, Object> paraMap, String tableName,Class clazz) {
		if (tableName.indexOf("_") == -1) {
			tableName = NameHandler.getTableName(tableName.toUpperCase());
		} else {
			tableName = tableName.toUpperCase();
		}
		Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName,clazz);
		paraMap.remove("sql");
		SQL sql = new SQL();
		sql.INSERT_INTO(tableName);
		for (String key : paraMap.keySet()) {
			String columnName = NameHandler.getColumnName(key);
			columnName = tableColumns.get(key)==null?"":tableColumns.get(key).toString();
			/*if (tableColumns.contains(columnName)) {
				sql.VALUES(columnName, "#" + key + "#");
			} else if (tableColumns.contains(columnName + "_")) {
				sql.VALUES(columnName + "_", "#" + key + "#");
			} else {
				continue;
			}*/
			if(!columnName.equals("")) {
				sql.VALUES(columnName, "#" + key + "#");
			}
		}
		this.getSQLScript(sql.toString()).insert(paraMap);
	}

	@Override
	public void update(Object entity) {
		String tableName = NameHandler.getTableName(entity.getClass().getSimpleName());
		Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName,entity.getClass());
		Map<String, Object> paraMap = MyBatisSqlUtils.buildUpdateSql(entity, tableColumns, "", null);
		this.getSQLScript(paraMap.get("sql").toString()).update(paraMap.remove("sql"));
		Class superClass = entity.getClass().getSuperclass();
		if (!superClass.getSimpleName().equals("Object")) {
			tableName = NameHandler.getTableName(superClass.getSimpleName());
			tableColumns = systemService.getHeroTableColumns(tableName,superClass);
			Map<String, Object> paraMapSupper = MyBatisSqlUtils.buildUpdateSql(entity, tableColumns,
					superClass.getSimpleName(), null);
			this.getSQLScript(paraMapSupper.get("sql").toString()).update(paraMapSupper.remove("sql"));
		}
	}

	@Override
	public void update(Object entity, String suffix) {
		String tableName = NameHandler.getTableName(entity.getClass().getSimpleName());
		tableName = tableName + "_" + suffix.toUpperCase();
		Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName,entity.getClass());
		Map<String, Object> paraMap = MyBatisSqlUtils.buildUpdateSql(entity, tableColumns, tableName, null);
		this.getSQLScript(paraMap.get("sql").toString()).update(paraMap.remove("sql"));
		Class superClass = entity.getClass().getSuperclass();
		if (!superClass.getSimpleName().equals("Object")) {
			tableName = NameHandler.getTableName(superClass.getSimpleName());
			tableName = tableName + "_" + suffix.toUpperCase();
			tableColumns = systemService.getHeroTableColumns(tableName,superClass);
			Map<String, Object> paraMapSupper = MyBatisSqlUtils.buildUpdateSql(entity, tableColumns, tableName, null);
			this.getSQLScript(paraMapSupper.get("sql").toString()).update(paraMapSupper.remove("sql"));

		}
		// jdbcDao.insert(entity);
	}

	@Override
	public void updateField(Map<String, Object> paraMap, Map<String, Object> whereMap, Class clazz) {
		String tableName = NameHandler.getTableName(clazz.getSimpleName());
		Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName,clazz);
		paraMap.remove("sql");
		SQL sql = new SQL();
		sql.UPDATE(tableName);
		for (String key : paraMap.keySet()) {
			String columnName = NameHandler.getColumnName(key);
			String sKey = "=#" + key + "#";
			if (paraMap.get(key) instanceof Date) {
				sKey = "=#" + key + ",jdbcType=TIMESTAMP#";
			}
			columnName = tableColumns.get(key)==null?"":tableColumns.get(key).toString();
			/*if (tableColumns.contains(columnName)) {
				sql.SET(columnName + sKey);
			} else if (tableColumns.contains(columnName + "_")) {
				sql.SET(columnName + "_" + sKey);
			} else {
				continue;
			}*/
			if(!columnName.equals("")) {
				sql.SET(columnName + sKey);
			}
		}
		for (String key : whereMap.keySet()) {
			String columnName = NameHandler.getColumnName(key);
			columnName = tableColumns.get(key)==null?"":tableColumns.get(key).toString();
			/*if (tableColumns.contains(columnName)) {
				sql.WHERE(columnName + "=#" + key + "#");
			} else if (tableColumns.contains(columnName + "_")) {
				sql.WHERE(columnName + "_" + "=#" + key + "#");
			} else {
				continue;
			}*/
			if(!columnName.equals("")) {
				sql.WHERE(columnName + "=#" + key + "#");
			}
		}
		if (null == whereMap || whereMap.isEmpty()) {
			String key="id";
			String columnName = tableColumns.get(key)==null?"":tableColumns.get(key).toString();
			/*if (tableColumns.contains("ID")) {
				sql.WHERE("id=#id#");
			} else if (tableColumns.contains("ID_")) {
				sql.WHERE("id_=#id#");
			}*/
			if(!columnName.equals("")) {
				sql.WHERE(columnName+"=#id#");
			}
		}
		paraMap.putAll(whereMap);
		this.getSQLScript(sql.toString()).update(paraMap);
	}

	@Override
	public Long getCountForJdbc(String sql, Map... params) {
		Map para = new HashMap();
		para.put("sql", sql);
		if (params != null && params.length == 1) {
			para.putAll(params[0]);
		}
		return this.getSQLScript(sql.toString()).selectCount(para);
	}
	@Override
	public Long getCount(String tableName, Map<String, Object> whereMap) {
		SQL sql = new SQL();
		Map<String, Object> sqlMap = new HashMap<String, Object>();
		if (whereMap != null && !whereMap.isEmpty()) {
			if (!tableName.contains("_")) {
				tableName = NameHandler.getTableName(tableName);
			}
			sql.SELECT("count(1)");
			sql.FROM(tableName);
			for (String key : whereMap.keySet()) {

				String columnName = NameHandler.getColumnName(key);
				if (tableName.startsWith("Act") && !columnName.endsWith("_")) {
					columnName = NameHandler.getColumnName(key + "_");
				}
				sql.WHERE(columnName + "=#" + key + "#");
				Object value = whereMap.get(key);
				sqlMap.put(key.toString(), value);
			}
		}
		if (!sql.toString().trim().equals("")) {
			return this.getSQLScript(sql.toString()).selectCount(whereMap);
		} else {
			return Long.parseLong("0");
		}
	}

	/**
	 * 
	 * @Title: selectListByPageSql @Description:分页获取对象数据 @param @param
	 *         sql @param @param entityClass @param @param para @param page
	 *         当前页数 @param limit 每页显示条数 @param @return 设定文件 @return List<T>
	 *         返回类型 @throws
	 */
	@Override
	public <T> List<T> findForJdbcPage(String sql, Class<T> clazz, Map<String, Object> para, int page, int rows) {
		List results = this.getSQLScript(sql).select(para, clazz, null, page, rows);
		return results;
	}
	@Override
	public <T> List<T> findByQueryString(String sql, Class<T> entityClass, Map<String, Object> para) {
		return sqlManager.execute(sql, entityClass, para);
	}
	@Override
	public <T> List<T> findByQueryString(Class<T> entityClass, Map<String, Object> para) {
		String tableName = NameHandler.getTableName(entityClass.getSimpleName());
		Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName,entityClass);
		if (null == para) {
			para = new HashMap();
		}
		para.remove("sql");
		SQL sql = new SQL();
		sql.SELECT("*");
		sql.FROM(tableName);
		for (Map.Entry<String, Object> entry : para.entrySet()) {
			String propertyName = entry.getKey();
			if (!propertyName.trim().equals("orderBy")) {
				String columnName = NameHandler.getColumnName(propertyName);
				 columnName = tableColumns.get(propertyName)==null?"":tableColumns.get(propertyName).toString();
				/*if (tableColumns.contains(columnName)) {
					sql.WHERE(columnName + "=#" + propertyName + "#");
				} else if (tableColumns.contains(columnName + "_")) {
					sql.WHERE(columnName + "_" + "=#" + propertyName + "#");
				} else {
					continue;
				}*/
				if(!columnName.equals("")) {
					sql.WHERE(columnName + "=#" + propertyName + "#");
				}
			}
		}
		List<Map<String, Object>> mapList;
		PageQuery query = new PageQuery(1, 10000);
		if (para.get("orderBy") != null) {
			String orderBy = (String) para.get("orderBy");
			orderBy = orderBy.trim();
			String order[] = orderBy.split(" ");
			sql.ORDER_BY(order[0] + " " + order[order.length - 1]);
		}
		return this.getSQLScript(sql.toString()).select(entityClass, para);
	}
	@Override
	public <T> Long findByQueryStringCount(Class<T> entityClass, Map<String, Object> para) {
		String tableName = NameHandler.getTableName(entityClass.getSimpleName());
		Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName,entityClass);
		if (null == para) {
			para = new HashMap();
		}
		para.remove("sql");
		SQL sql = new SQL();
		sql.SELECT("count(*)");
		sql.FROM(tableName);
		for (Map.Entry<String, Object> entry : para.entrySet()) {
			String propertyName = entry.getKey();
			if (!propertyName.trim().equals("orderBy")) {
				String columnName = NameHandler.getColumnName(propertyName);
				columnName = tableColumns.get(propertyName)==null?"":tableColumns.get(propertyName).toString();
				/*if (tableColumns.contains(columnName)) {
					sql.WHERE(columnName + "=#" + propertyName + "#");
				} else if (tableColumns.contains(columnName + "_")) {
					sql.WHERE(columnName + "_" + "=#" + propertyName + "#");
				} else {
					continue;
				}*/
				if(!columnName.equals("")) {
					sql.WHERE(columnName + "=#" + propertyName + "#");
				}
			}
		}
		return this.getSQLScript(sql.toString()).selectCount(para);
	}

	@Override
	public void delete(Class obj, Object id) {
		String tableName = NameHandler.getTableName(obj.getSimpleName());
		Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName,obj);
		SQL sql = new SQL();
		sql.DELETE_FROM(tableName);
		String propertyName="id";
		String columnName = tableColumns.get(propertyName)==null?"":tableColumns.get(propertyName).toString();
		/*if (tableColumns.contains("ID")) {
			sql.WHERE("id=#id#");
		} else if (tableColumns.contains("ID_")) {
			sql.WHERE("id_=#id#");
		}*/
		if(!columnName.equals("")) {
			sql.WHERE(columnName+"=#id#");
		}
		Map paras = new HashMap();
		paras.put("id", id);
		sqlManager.executeUpdate(sql.toString(), paras);
	}
	@Override
	public List<String> getStringListBySql(Class obj, String selColName, Map<String, Object> para, String orderCol,
			String orderBy) {
		String tableName = NameHandler.getTableName(obj.getSimpleName());
		Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName,obj);
		String selColumn = NameHandler.getColumnName(selColName);
		if (null == para) {
			para = new HashMap();
		}
		para.remove("sql");
		SQL sql = new SQL();
		String columnName = tableColumns.get(selColName)==null?"":tableColumns.get(selColName).toString();
		if(!columnName.equals("")) {
			selColumn = columnName;
		}
		/*if (tableColumns.contains(selColumn)) {
			selColumn = selColumn;
		} else if (tableColumns.contains(selColumn + "_")) {
			selColumn = selColumn + "_";
		}*/
		sql.SELECT(selColumn);
		sql.FROM(tableName);
		for (Map.Entry<String, Object> entry : para.entrySet()) {
			String propertyName = entry.getKey();
			//String columnName = NameHandler.getColumnName(propertyName);
			columnName = systemService.getTableColumn(tableName, columnName,obj);
			/*if (tableColumns.contains(columnName)) {
				sql.WHERE(columnName + "=#" + propertyName + "#");
			} else if (tableColumns.contains(columnName + "_")) {
				sql.WHERE(columnName + "_" + "=#" + propertyName + "#");
			} else {
				continue;
			}*/
			if(!columnName.equals("")) {
				sql.WHERE(columnName + "=#" + propertyName + "#");
			}
		}
		if (orderCol != null && !orderCol.trim().equals("")) {
			String orderColumn = NameHandler.getColumnName(orderCol);
			orderColumn = tableColumns.get(orderCol)==null?"":tableColumns.get(orderCol).toString();
			/*if (tableColumns.contains(orderColumn)) {
				orderColumn = orderColumn;
			} else if (tableColumns.contains(orderColumn + "_")) {
				orderColumn = orderColumn + "_";
			}*/
			 if(!orderColumn.equals("")) {
				 orderColumn = orderColumn;
			 }
			if (orderBy == null || orderBy.trim().equals("")) {
				orderBy = " asc";
			}
			sql.ORDER_BY(orderColumn + " " + orderBy);
		}
		return sqlManager.execute(sql.toString(), obj, para);

	}
	@Override
	public List<String> getStringListBySql(String sql, Map<String, Object> para) {
		return getSQLScript(sql).select(String.class, para);
	}

	@Override
	public void delete(Class obj, Map<String, Object> para) {
		String tableName = NameHandler.getTableName(obj.getSimpleName());
		Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName,obj);
		SQL sql = new SQL();
		sql.DELETE_FROM(tableName);
		for (Map.Entry<String, Object> entry : para.entrySet()) {
			String propertyName = entry.getKey();
			String columnName = NameHandler.getColumnName(propertyName);
			columnName = systemService.getTableColumn(tableName, columnName,obj);
			sql.WHERE(columnName + "=#" + propertyName + "#");
			/*if (tableColumns.contains(columnName)) {
				sql.WHERE(columnName + "=#" + propertyName + "#");
			} else if (tableColumns.contains(columnName + "_")) {
				sql.WHERE(columnName + "_" + "=#" + propertyName + "#");
			} else {
				continue;
			}*/
		}
		sqlManager.executeUpdate(sql.toString(), para);
	}

	@Override
	public void delete(String tableName, String columnName, Object value) {
		SQL sql = new SQL();
		sql.DELETE_FROM(tableName);
		sql.WHERE(columnName + "=#" + columnName + "#");
		Map paras = new HashMap();
		paras.put(columnName, value);
		sqlManager.executeUpdate(sql.toString(), paras);
	}

	@Override
	public void deleteBySql(String sql, Map para) {
		sqlManager.executeUpdate(sql, para);
	}

	@Override
	public Integer executeSql(String sql, Map<String, Object> param) {
		// return jdbcDao.executeSql(sql, param);
		// sqlManager.getScript(id);
		return sqlManager.executeUpdate(sql, param);
	}

	@Override
	public Integer updateMap(String tableName, Map<String, Object> param, Map whereMap) {
		Map<String, Object> paraMap = HeroSqlUtils.buildBeetlUpdateSql(tableName, param, whereMap);
		String sql = paraMap.get("sql").toString();
		// Map parasMap=(Map)paraMap.get("parasMap");
		paraMap.remove("sql");
		return sqlManager.executeUpdate(sql, paraMap);
	}

	@Override
	public Integer insertMap(String tableName, Map<String, Object> param) {
		Map<String, Object> paraMap = HeroSqlUtils.buildBeetlInsertSql(tableName, param);
		String sql = paraMap.get("sql").toString();
		paraMap.remove("sql");
		return sqlManager.executeUpdate(sql, paraMap);
	}

	@Override
	public <T> List<T> loadAll(Class<T> entityClass) {
		String tableName = NameHandler.getTableName(entityClass.getSimpleName());
		String sql = "select * from " + tableName;
		Map paras = new HashMap();
		return getSQLScript(sql).select(entityClass, null);
	}

	@Override
	public int executeSQL(String sql) {
		return sqlManager.executeUpdate(sql, null);
	}

	@Override
	public DataGrid getDataGridReturn(String sql, Map para, String select, Class clazz, int page, int rows) {
		DataGrid dataGrid = new DataGrid();
		List results = this.getSQLScript("select " + select + " " + sql).select(para, clazz, null, page, rows);
		long total = this.getSQLScript("select count(1) " + sql).selectCount(para);
		dataGrid.setTotal(Integer.parseInt(total + ""));
		dataGrid.setResults(results);
		return dataGrid;
	}
	@Override
	public void batchInsert(Class entityClass, List list) {
		sqlManager.insertBatch(entityClass, list);
	}
	@Override
	public void batchUpdate(String sql, List<Map> list) {
		getSQLScript(sql).updateBatch(list);
	}

	private SQLScript getSQLScript(String sql) {
		String key = "auto._gen_" + sql;
		SQLSource source = sqlManager.getSqlLoader().getSQL(key);
		if (source == null) {
			source = new SQLSource(key, sql);
			sqlManager.getSqlLoader().addSQL(key, source);
		}

		SQLScript script = new SQLScript(source, sqlManager);
		return script;
	}

	@Override
	public List<Map> findForJdbc(String sql, Map para) {

		return getSQLScript(sql).select(Map.class, para);
	}

	@Override
	public Map findOneForJdbc(String sql, Map para) {
		return getSQLScript(sql).selectSingle(para, Map.class);
	}

	@Override
	public <T> T findOneObjForJdbc(String sql, Map para, Class<T> clazz) {
		return (T) getSQLScript(sql).selectSingle(para, clazz);
	}
}
